<?php     
    header("Content-type: text/html; charset=utf-8;");
    ini_set('memory_limit', '-1');
    require_once('../../../cfg/config.inc');
    require_once( '../../../ppf/pdo_mysql.php'); 
    require_once('./excel/PHPExcel.php');
	require_once('./excel/PHPExcel/IOFactory.php');
    require_once('./excel/PHPExcel/Reader/Excel5.php');
	require_once('./excel/PHPExcel/Writer/Excel5.php');
	function p($res){#打印结果
		echo "<pre>";
		print_r($res);
		echo "</pre>";	
	}
	//二维数组排序， $arr是数据，$keys是排序的健值，$order是排序规则，0是升序，1是降序
	function array_sort($arr, $keys, $order=0) {
	if (!is_array($arr)) {
	return false;
	}
	$keysvalue = array();
	foreach($arr as $key => $val) {
	$keysvalue[$key] = $val[$keys];
	}
	if($order == 0){
	asort($keysvalue);
	}else {
	arsort($keysvalue);
	}
	reset($keysvalue);
	foreach($keysvalue as $key => $vals) {
	$keysort[$key] = $key;
	}
	$new_array = array();
	foreach($keysort as $key => $val) {
	$new_array[$key] = $arr[$val];
	}
	return $new_array;
	}
	
/********************************************************************************/	
	
	
    $pd=new pdo_mysql();
    // $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
    // $cacheSettings = array (
    // 'cacheTime' => 300 
    // );
    // PHPExcel_Settings::setCacheStorageMethod ( $cacheMethod, $cacheSettings );
      //接收前台文件  
    $ex = $_FILES['excel'];  
    //重设置文件名  
    $filename = time().".".strtolower(pathinfo($ex['name'], PATHINFO_EXTENSION)); 
    $dir = DIR_ROOT.'/upload/excel/';
    $path = $dir.$filename;//设置移动路径 
    if(!is_dir($dir)){
        $re=mkdir($dir,0777,true);#第3个参数为创建多级目录
        if(!$re){
            echo '{"ret":"err","errno":"6","msg":"创建目录错误，没有权限"}';
            exit;
        }
    }    
    $res = move_uploaded_file($ex['tmp_name'],$path);
    if($res){
        $objReader = PHPExcel_IOFactory::createReader('Excel5');/*Excel5 for 2003 excel2007 for 2007*/  
		$objPHPExcel = $objReader->load($path); //Excel 路径  
		$sheet = $objPHPExcel->getSheet(0);  
		$highestRow = $sheet->getHighestRow(); // 取得总行数  
		$highestColumn = $sheet->getHighestColumn(); // 取得总列数 
		$objWorksheet = $objPHPExcel->getActiveSheet();          
		$highestRow = $objWorksheet->getHighestRow();   // 取得总行数       
		$highestColumn = $objWorksheet->getHighestColumn();          
		$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);//总列数  
		$strs=array();
		$arr1=array();
		$i=0;
		for ($row = 2;$row <= $highestRow;$row++)         {  
			//$strs=array();  
			//注意highestColumnIndex的列数索引从0开始  
			for ($col = 0;$col < $highestColumnIndex;$col++)            {  
				$strs[$col] = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue(); 
				
			}
			$arr1[$i]=$strs;
			$i++;
		}
		$pd->exec("TRUNCATE TABLE class_placement_stu");
		foreach ($arr1 as $k => $val) {
			if($val[7] >= 200){
				$rs = $pd->exec("INSERT INTO class_placement_stu(school,name,sex,chinese,math,english,total) VALUES(
				'".$val[1]."',
				'".$val[2]."',
				'".$val[3]."',
				'".$val[4]."',
				'".$val[5]."',
				'".$val[6]."',
				'".$val[7]."')");
			}
		}
		
    }
	//$stu_total=$highestRow-1;
	//echo $stu_total."<br/>";//学生总人数
	$stu_total=$pd->query("SELECT COUNT(1) FROM class_placement_stu")->fetchColumn(0);
	//echo $stu_total."<br/>";
	$cls_total=15;//班级总数
	$cls_stu=floor($stu_total/$cls_total);//平均每个班的学生人数
	for($i=0;$i<$cls_stu;$i++){
		$start=$i*$cls_total;
		$stmt=$pd->query("SELECT id FROM class_placement_stu WHERE class IS NULL ORDER BY total DESC LIMIT 0,".$cls_total);
		$j=1;
		$k=$cls_total;
		if($i%2==0){
			while($ord=$stmt->fetch(PDO::FETCH_ASSOC)){
				$pd->exec("UPDATE class_placement_stu SET class = ".($j++)." WHERE id = ".$ord['id']);
			}
		}else{
			while($ord=$stmt->fetch(PDO::FETCH_ASSOC)){
				$pd->exec("UPDATE class_placement_stu SET class = ".($k--)." WHERE id = ".$ord['id']);
			}
		}	
	}
	$boys_all=$pd->query("SELECT COUNT(1) FROM class_placement_stu WHERE sex='男'")->fetchColumn(0);//全部男生人数
	$girls_all=$pd->query("SELECT COUNT(1) FROM class_placement_stu WHERE sex='女'")->fetchColumn(0);//全部女生人数
	$sex_cmp=ceil(abs($boys_all-$girls_all)/$cls_total);//平均男女生差值
	//全校总分
	$score_total=$pd->query("SELECT SUM(total) FROM class_placement_stu")->fetchColumn(0);
	//全校平均分
	$score_ave=$score_total/$stu_total;
	for($i=1;$i<$cls_total;$i++){
		$boys=$pd->query("SELECT COUNT(1) FROM class_placement_stu WHERE sex='男' AND class=".$i)->fetchColumn(0);//每班男生总数
		$girls=$pd->query("SELECT COUNT(1) FROM class_placement_stu WHERE sex='女' AND class=".$i)->fetchColumn(0);//每班女生总数
		/*$score_total=$pd->query("SELECT SUM(total) FROM class_placement_stu WHERE class=".$i)->fetchColumn(0);//每班总分
		$score_ave=$score_total/$cls_stu;//每班平均分*/
		$changes=floor(abs($boys-$girls)/2);//每次需要调动的人数
		//echo $i."班男生:".$boys."人,女生:".$girls."人<br/>";
		if(($boys-$girls)>$sex_cmp){//男生比女生多时
			//选择被交换的男生
			$boy_chose=$pd->query("SELECT id,class FROM class_placement_stu WHERE class=".$i." AND sex='男' AND total<".$score_ave." LIMIT ".$changes)->fetchAll();
			//选择被交换的女生
			$girl_chose=$pd->query("SELECT id,class FROM class_placement_stu WHERE class>".$i." AND sex='女' AND total<".$score_ave." LIMIT ".$changes)->fetchAll();
			if($boy_chose && $girl_chose){
				$boy_ids=array();
				foreach($boy_chose as $v){
					$boy_ids[]=$v['id'];
				}
				$girl_ids=array();
				$girl_classes=array();
				foreach($girl_chose as $v){
					$girl_ids[]=$v['id'];
					$girl_classes[]=$v['class'];
				}
				$boy_count=count($boy_ids);
				$girl_count=count($girl_ids);
				if($boy_count>=$girl_count){
					//需要交换的男生比女生多的时候,只交换女生人数个男生
					$boy_ids=array_slice($boy_ids,0,$girl_count);
				}
				$sql="";
				//p($boy_ids);
				foreach($boy_ids as $k=>$v){
					$sql.="update class_placement_stu set class=".$girl_classes[$k]." where id=".$v.";";
				}
				//echo $sql."<br/>";
				if($sql!=''){
					$pd->exec($sql);
					$girl_ids=implode(',',$girl_ids);
					$sql="update class_placement_stu set class=".$i." where id in (".$girl_ids.")";
					$pd->exec($sql);
				}
			}
		}elseif(($girls-$boys)>$sex_cmp){//女生比男生多时
			$girl_chose=$pd->query("SELECT id,class FROM class_placement_stu WHERE class=".$i." AND sex='女' AND total<".$score_ave." LIMIT ".$changes)->fetchAll();
			$boy_chose=$pd->query("SELECT id,class FROM class_placement_stu WHERE class>".$i." AND sex='男' AND total<".$score_ave." LIMIT ".$changes)->fetchAll();
			if($boy_chose && $girl_chose){
				$boy_ids=array();
				$boy_classes=array();
				foreach($boy_chose as $v){
					$boy_ids[]=$v['id'];
					$boy_classes[]=$v['class'];
				}
				$girl_ids=array();
				foreach($girl_chose as $v){
					$girl_ids[]=$v['id'];
				}
				$boy_count=count($boy_ids);
				$girl_count=count($girl_ids);
				if($girl_count>=$boy_count){
					//需要交换的女生比男生多的时候,只交换男生人数个女生
					$girl_ids=array_slice($girl_ids,0,$boy_count);
				}
				$sql="";
				//p($girl_ids);
				foreach($girl_ids as $k=>$v){
					$sql.="update class_placement_stu set class=".$boy_classes[$k]." where id=".$v.";";
				}
				//echo $sql."<br/>";
				if($sql!=''){
					$pd->exec($sql);
					$boy_ids=implode(',',$boy_ids);
					$sql="update class_placement_stu set class=".$i." where id in ({$boy_ids})";
					$pd->exec($sql);
				}
			}
		}
	}
	foreach ($arr1 as $k => $val) {
		if($val[7] < 200){
			$rs = $pd->exec("INSERT INTO class_placement_stu(school,name,sex,chinese,math,english,total) VALUES(
			'".$val[1]."',
			'".$val[2]."',
			'".$val[3]."',
			'".$val[4]."',
			'".$val[5]."',
			'".$val[6]."',
			'".$val[7]."')");
		}
	}
	/*
	//导出Excel
	$excel = new PHPExcel();
	//Excel表格式
    $letter = array('A','B','C','D','E','F','G','H','I');
    //表头数组
    $tableheader = array('编号','学校','姓名','性别','语文','数学','英语','三门总分','班级');
    //填充表头信息
    for($i = 0;$i < count($tableheader);$i++) {
        $excel->getActiveSheet()->setCellValue("$letter[$i]1","$tableheader[$i]");
    }
	$sql="SELECT * FROM class_placement_stu ORDER BY class ASC";
	$attr = $pd->query($sql)->fetchall(PDO::FETCH_ASSOC);
	//填充表格信息
    for ($i = 2;$i <= count($attr) + 1;$i++) {
        $j = 0;
        foreach ($attr[$i - 2] as $key=>$value) {
            $excel->getActiveSheet()->setCellValue("$letter[$j]$i","$value");
            $j++;
        }
    }
    //创建Excel输入对象
    $write = new PHPExcel_Writer_Excel5($excel);
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
    header("Content-Type:application/force-download");
    header("Content-Type:application/vnd.ms-execl");
    header("Content-Type:application/octet-stream");
    header("Content-Type:application/download");;
    header("Content-Disposition:attachment;filename='stu.xls'");
    header("Content-Transfer-Encoding:binary");
    $write->save('php://output');
	*/
	echo "<table width='800' border='1' cellpadding='0' cellspacing='0'>";
	echo "<tr><td>编号</td><td>学校</td><td>姓名</td><td>性别</td><td>语文</td><td>数学</td><td>英语</td><td>三门总分</td><td>班级</td></tr>";
	$sql = "SELECT * FROM class_placement_stu WHERE class IS NOT NULL	ORDER BY class ASC";
	$arr2 = $pd->query($sql)->fetchall(PDO::FETCH_ASSOC);
	foreach($arr2 as $key=>$val){
		echo "<tr><td>".$val['id']."</td><td>".$val['school']."</td><td>".$val['name']."</td><td>".$val['sex']."</td><td>".$val['chinese']."</td><td>".$val['math']."</td><td>".$val['english']."</td><td>".$val['total']."</td><td>".$val['class']."</td></tr>";
	}
	echo "</table>";
	$pd->close();
	unset($pd);
?>